# load modules and functions 
import os
import numpy as np
import pandas as pd
from pandas import DataFrame

import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go

from colorama import Fore, Back, Style
from datetime import time, timedelta

# load DataFrames:
spend = pd.read_pickle("03_spend.pkl")
deals = pd.read_pickle("04_deals.pkl")

# Calculation of the number of leads for each source
source_leads = deals.groupby('Source', observed=True).size().reset_index(name='Leads')

# Calculation of the number of successful deals for each source
success_deals = deals[deals['Stage'] == 'Payment Done'].groupby('Source', observed=True).size().reset_index(name='Success Deals')

# Merging data for conversion rate calculation
source_performance = pd.merge(source_leads, success_deals, on='Source', how='left')

# Calculating conversion rate, considering missing values
source_performance['Success Deals'] = source_performance['Success Deals'].fillna(0)
source_performance['Conversion Rate (%)'] = source_performance['Success Deals'] / source_performance['Leads'] * 100

# Set float display format
pd.options.display.float_format = '{:.2f}'.format

source_performance = source_performance.sort_values(by="Leads", ascending=False)
display(source_performance)

import plotly.express as px

# Create a chart with two scales
fig = px.bar(source_performance, 
             x="Source", 
             y="Leads",
             title="Marketing Sources Performance",
             text_auto=True,
             labels={"Leads": "Leads number"},
             color_discrete_sequence=["rgba(31, 119, 150, 0.8)", "#ff7f0e80", "rgba(44, 160, 44, 0.7)"]
             #color_discrete_sequence=["blue"]
            )
# Add a name to display the bar chart in the legend
fig.update_traces(name="Leads number", showlegend=True)

# Add a line for Conversion Rate (%) with a secondary axis and labels
fig.add_scatter(x=source_performance["Source"], 
                y=source_performance["Conversion Rate (%)"], 
                mode="lines+markers+text",
                name="Conversion Rate (%)",
                yaxis="y2",
                marker=dict(color="orange", size=8),
                text=[f"{x:.2f}%" for x in source_performance["Conversion Rate (%)"]],  # Подписи данных                
                textposition="top center"                
               )  


fig.update_layout(
    yaxis=dict(title="Leads", showgrid=False),
    yaxis2=dict(title="Conversion Rate (%)", overlaying="y", side="right", showgrid=True),
    xaxis=dict(title="Marketing Sources", tickangle=45),   
    legend=dict(x=0.4, y=0.99),
    #width=1000,
    #height=600
)

fig.show()

# Calculate deal duration
deals["Closing_Duration"] = (deals["Closing_Date"] - deals["Created_Time"]).dt.days
deals.loc[deals["Closing_Duration"] < 0, "Closing_Duration"] = 0

# Group data by marketing source
source_stats = deals.groupby("Source", observed=True).agg(    
    Leads_Generated=("Id", "count"),                                
    Deals_Closed=("Stage", lambda x: (x == "Payment Done").sum()),  
    Revenue=("Paid", lambda x: x[deals["Stage"] == "Payment Done"].sum()),     
    Average_Deal_Value=("AOV", lambda x: x[deals["Stage"] == "Payment Done"].mean() if x[deals["Stage"] == "Payment Done"].sum() > 0 else 0),
    Closing_Duration=("Closing_Duration", "mean") # Average deal closing duration
).reset_index()

# Fill missing closing duration values with 0
source_stats['Closing_Duration'] = source_stats['Closing_Duration'].fillna(0)

# Calculate conversion rate (%)
source_stats["Conversion_Rate"] = (source_stats["Deals_Closed"] / source_stats["Leads_Generated"]) * 100

# Sort by Leads_Generated
source_stats = source_stats.sort_values(by="Leads_Generated", ascending=False)

# Set float display format
pd.options.display.float_format = '{:.2f}'.format

print(Back.YELLOW +"Marketing Source performance indicators - summary table" + Style.RESET_ALL)
display(source_stats)

Marketing Source performance indicators - summary table

# import ipywidgets as widgets
# from IPython.display import display

# # Function for scatterplot with dynamic parameter selection
# def update_plot(metric):
#     plt.figure(figsize=(12, 6))
#     ax = sns.scatterplot(data=source_stats, x="Leads_Generated", y="Conversion_Rate", hue="Source", size=source_stats[metric], sizes=(90, 500))
#     for i, row in source_stats.iterrows():
#         plt.text(row["Leads_Generated"], row["Conversion_Rate"], row["Source"], 
#                  fontsize=8, ha="right", va="top", color="black")    
#     plt.xlabel("Leads Generated")
#     plt.ylabel("Conversion Rate (%)")
#     plt.title(f"Marketing Source Effectiveness: Leads vs Conversion Rate and {metric}")
#     plt.legend(loc="upper right", bbox_to_anchor=(1.3, 1))
#     plt.show()

# # Creating a dropdown switch
# metric_selector = widgets.Dropdown(
#     options=["Deals_Closed", "Revenue"],
#     value="Deals_Closed",
#     description="Metric:",
#     style={'description_width': 'initial'}
# )

# # Updating the scatterplot when the value in dropdown changes
# widgets.interactive(update_plot, metric=metric_selector)

# for REPORT without drop-down
import ipywidgets as widgets
from IPython.display import display

plt.figure(figsize=(12, 6))
ax = sns.scatterplot(data=source_stats, x="Leads_Generated", y="Conversion_Rate", hue="Source", size="Revenue", sizes=(90, 500))
for i, row in source_stats.iterrows():
    plt.text(row["Leads_Generated"], row["Conversion_Rate"], row["Source"], 
             fontsize=8, ha="right", va="top", color="black")    
plt.xlabel("Leads Generated")
plt.ylabel("Conversion Rate (%)")
plt.title(f"Marketing Source Effectiveness: Leads vs Conversion Rate and Revenue")
plt.legend(loc="upper right", bbox_to_anchor=(1.3, 1))
plt.show()

import plotly.graph_objects as go
from plotly.subplots import make_subplots

# Create subplots: 1 row, 2 columns
fig = make_subplots(rows=1, cols=2, subplot_titles=["Average Closing Duration by Source", 
                                                    "Average Deal Value by Source"]
)

source_stats = source_stats.sort_values(by="Closing_Duration", ascending=False)

# Bar plot: Closing Duration
fig.add_trace(
    go.Bar(
        x=source_stats["Source"],
        y=source_stats["Closing_Duration"],
        text=[f"{x:.1f}" for x in source_stats["Closing_Duration"]],  # Adding labels
        textposition="outside",
        marker=dict(color="brown", opacity=0.8),
    ),
    row=1, col=1
)

source_stats = source_stats.sort_values(by="Average_Deal_Value",ascending=False)
# Bar plot: Average Deal Value
fig.add_trace(
    go.Bar(
        x=source_stats["Source"],
        y=source_stats["Average_Deal_Value"],
        text=[f"{x:.0f}" for x in source_stats["Average_Deal_Value"]],  # Adding labels
        textposition="outside",
        marker=dict(color="purple", opacity=0.8),
    ),
    row=1, col=2
)

# Layout settings
fig.update_layout(
    title="Marketing Source Performance",
    showlegend=False,
    #height=500,
    #width=1000,
    xaxis1=dict(title="", tickangle=45),
    xaxis2=dict(title="", tickangle=45),
    yaxis1=dict(title="Closing Duration (days)"),
    yaxis2=dict(title="Average Deal Value (€)"),
)

fig.show()

# Group data by marketing source (Source)
campaign_stats = deals.groupby("Campaign", observed=True).agg(
    Leads_Generated=("Id", "count"),                                
    Deals_Closed=("Stage", lambda x: (x == "Payment Done").sum()),  
    #Revenue=("Paid", lambda x: x[deals["Stage"] == "Payment Done"].sum()),     
    Average_Deal_Value=("AOV", lambda x: x[deals["Stage"] == "Payment Done"].mean() if x[deals["Stage"] == "Payment Done"].sum() > 0 else 0),
    Closing_Duration=("Closing_Duration", "mean") # Average deal closing duration
).reset_index()


# Fill missing closing duration values with 0
campaign_stats['Closing_Duration'] = campaign_stats['Closing_Duration'].fillna(0)
campaign_stats['Average_Deal_Value'] = campaign_stats['Average_Deal_Value'].fillna(0)


# # Calculate conversion rate (%)
campaign_stats["Conversion_Rate"] = (campaign_stats["Deals_Closed"] / campaign_stats["Leads_Generated"]) * 100

# Sort by Leads_Generated
campaign_stats = campaign_stats.sort_values(by="Leads_Generated", ascending=False)


# Set float display format
pd.options.display.float_format = '{:.2f}'.format

print(Back.CYAN +"Marketing Campaign performance indicators - summary table" + Style.RESET_ALL)
display(campaign_stats)

Marketing Campaign performance indicators - summary table

# import plotly.express as px
# import ipywidgets as widgets
# from IPython.display import display

# total_campaign = deals['Campaign'].nunique()

# # Graphics update function
# def update_chart(top_n):
#     top_campaigns = campaign_stats.sort_values(by="Leads_Generated", ascending=False).head(top_n)
    
#     # exclude from the bar-chart companies with names missing in the dataset
#     top_campaigns = top_campaigns[top_campaigns["Campaign"] != "unknown"]
    
#     fig = px.bar(top_campaigns, 
#                  x='Campaign',
#                  y='Leads_Generated', 
#                  title=f"Top {top_n} (from {total_campaign}) Campaign by Leads generation",
#                  text_auto=True)
#     fig.show()

# # Creating an interactive slider to select the number of campaigns
# top_n_selector = widgets.IntSlider(
#     value=15,  # Default value
#     min=5,     
#     max=55,    
#     step=5,    
#     description="Top N:"
# )

# widgets.interactive(update_chart, top_n=top_n_selector)

# for REPORT witout interactive slider
import plotly.express as px
import ipywidgets as widgets
from IPython.display import display

total_campaign = deals['Campaign'].nunique()
top_n = 15

top_campaigns = campaign_stats.sort_values(by="Leads_Generated", ascending=False).head(top_n)

# exclude from the bar-chart companies with names missing in the dataset
top_campaigns = top_campaigns[top_campaigns["Campaign"] != "unknown"]

fig = px.bar(top_campaigns, 
             x='Campaign',
             y='Leads_Generated', 
             title=f"Top {top_n} (from {total_campaign}) Campaign by Leads generation",
             text_auto=True)
fig.show()

import plotly.express as px
fitered_campaign_stats = campaign_stats[(campaign_stats.Conversion_Rate > 0) & (campaign_stats.Campaign != "unknown")]

# Создаем график с двумя шкалами
fig = px.bar(fitered_campaign_stats, 
             x="Campaign", 
             y="Leads_Generated",
             title="Marketing Campaigne Performance",
             text_auto=True,
             labels={"Leads": "Number of Leads"}            
            )
# Добавляем имя (name) для отображения бар-чарта в легенде
fig.update_traces(name="Leads number", showlegend=True)

# Добавляем линию для Conversion Rate (%) с вторичной осью и подписями
fig.add_scatter(x=fitered_campaign_stats["Campaign"], 
                y=fitered_campaign_stats["Conversion_Rate"], 
                mode="lines+markers",
                name="Conversion Rate (%)",
                yaxis="y2",
                marker=dict(color="orange", size=8),
                text=[f"{x:.2f}%" for x in fitered_campaign_stats["Conversion_Rate"]],  # Подписи данных                
                textposition="top center",  # Размещение подписей                
               ) 

# Настройки осей

fig.update_layout(
    yaxis=dict(title="Leads", showgrid=False),
    yaxis2=dict(title="Conversion Rate (%)", overlaying="y", side="right", showgrid=True),
    xaxis=dict(title="Marketing Campaigne", tickangle=45),   
    legend=dict(x=0.35, y=0.98),
    #width=1000,
    #height=600
)

fig.show()

deals_campaigns = set(deals["Campaign"].unique())
print(Fore.RED + f"deals contains {len(deals_campaigns)} campaigns\n")

spend_campaigns = set(spend["Campaign"].unique())
print(Fore.RED + f"spend contains {len(spend_campaigns)} campaigns\n")

deals_not_in_spend = deals_campaigns - spend_campaigns
spend_not_in_deals = spend_campaigns - deals_campaigns
common_campaigns = deals_campaigns.intersection(spend_campaigns)

print(Fore.BLUE + f"Campaigns in deals but not in spend:{len(deals_not_in_spend)}\
                    \n{sorted(deals_not_in_spend)}\n")
print(Fore.GREEN + f"Campaigns in spend but not in deals:{len(spend_not_in_deals)}\
                    \n{sorted(spend_not_in_deals)}\n")
print(Fore.MAGENTA + f"The number of campaigns is the same in both dataframes:{len(common_campaigns)}\
                    \n{sorted(common_campaigns)}"+ Style.RESET_ALL )

deals contains 155 campaigns

spend contains 52 campaigns

Campaigns in deals but not in spend:109                    
['1004start', '1005start', '1006start', '11.10_prizes_DE', '1406start', '1704start', '1706_DE', '2004start', '2005_Lost_DE', '26.10start', '29.11start', '2905start', '2day_DE', '5555_DE', 'ASA_de_DE', 'Akademia', 'Aussiedler_DE', 'BVRE', 'Berlin_DE', 'Blogff_DE', 'BloggerIvan', 'BloggerShina_DE', 'Blogger_snezh_DE', 'Bloggerel_DE', 'Bloggernina_DE', 'Bloggerperr_DE', 'Bolgspeak_DE', 'Consult_DE', 'Dis_DE', 'Forum_DE', 'Genie_DE', 'Jobs_germany_DE', 'Kak_PL', 'LOST_de', 'Live_DE', 'Markt_DE', 'PL_life', 'Relocat_DE', 'Trigger_DE', 'adv_DE', 'anastasia_de_DE', 'anuta_DE', 'arina_DE', 'berlin_DE', 'bf_DE', 'blog_DE', 'blog_de_DE', 'bloggerdr_DE', 'bloggerfrai_DE', 'chat_DE', 'clever_DE', 'col_DE', 'domain', 'eha_DE', 'engwien_AT', 'euro_DE', 'forum_DE', 'germania_DE', 'germany_DE', 'ggg10_DE', 'grad_DE', 'hands_DE', 'hanna', 'hb_DE', 'ineng_DE', 'jet_DE', 'karta_DE', 'liveberlin_DE', 'lviv_DE', 'magda_DE', 'mail_3', 'mail_DE', 'mailreg_DE', 'maria_DE', 'marue_DE', 'mu_DE', 'nina', 'nochtum_DE', 'of_DE', 'performancemax_digitalmarkt_ru_DE', 'pishet_DE', 'pl_com', 'podrugi_DE', 'podslush_DE', 'potsdam', 'price_DE', 'referral', 'ruslana_DE', 'sushechka_PL', 'talav_DE', 'tyk_DE', 'uk12_AT', 'uk_DE', 'wa_PL', 'warsaw_PL', 'web1312_DE', 'web2211_DE', 'web2311_DE', 'web2410_DE', 'web8_DE', 'webanons', 'webinar1604', 'webinar1906', 'welt_DE', 'women_DE', 'work_DE', 'work_wr', 'workingin_DE', 'yo_DE']

Campaigns in spend but not in deals:6                    
['01.02.24wide_webinar_DE', '07.12.23test_DE', '15.03.24recentlymoved_AT', '15.11.23wide_webinar_DE', '20.03.24recentlymoved_PL', '30.11.23wide_DE']

The number of campaigns is the same in both dataframes:46                    
['01.04.23women_PL', '02.05.24test_DE', '02.07.23wide_DE', '02.08.23interests_DE', '03.07.23women', '04.07.23recentlymoved_DE', '05.07.23interests_DE', '05.09.2023wide_DE', '07.07.23LAL_DE', '08.04.24wide_webinar_DE', '08.06.24wide_webinar_DE', '09.02.24berlin_dd_DE', '10.07.23wide_com_DE', '12.06.24wide_DE', '12.07.2023wide_DE', '12.09.23interests_Uxui_DE', '14.11.23wide_webinar_DE', '15.03.2024wide_AT', '15.04.24LAL_ab__PL', '15.07.23b_DE', '17.03.24wide_AT', '18.10.23wide_gos_DE', '1performancemax_wide_PL', '20.03.2024wide_PL', '20.03.24_widde_PL', '20.03.24interests_WebDev_AT', '20.03.24interests_WebDev_PL', '20.05.24interests_DE', '20.05.24wide_DE', '22.05.2024wide_DE', '24.07.2023wide_DE', '24.09.23retargeting_DE', 'bbo_DE', 'blog2_DE', 'brand_search_eng_DE', 'comp_search_DE', 'discovery_DE', 'discovery_wide1_AT', 'gen_analyst_DE', 'performancemax_eng_DE', 'performancemax_wide_AT', 'shorts_PL', 'unknown', 'web2408_DE', 'youtube_shorts_DE', 'youtube_shortsin_AT']

import plotly.express as px

# Grouping data from spend and deals by campaigns
campaign_analysis = spend.groupby("Campaign", observed=True).agg({'Spend': 'sum'}).reset_index()

deals['Source'] = deals['Source'].astype(str)  
deals_analysis = deals.groupby("Campaign", observed=True).agg({
    "Id": "count",  # Подсчет количества лидов
    "Stage": lambda x: (x == "Payment Done").sum(),  # Подсчет закрытых сделок
    'Source': lambda x: x.mode()[0] if not x.mode().empty else None  # Получаем моду (наиболее частое значение)
}).reset_index()

# rename the columns for consistency
deals_analysis.rename(columns={"Id": "Leads_Generated", "Stage": "Deals_Closed"}, inplace=True)

# Merge DataFrame spend and dealss
campaign_performance = campaign_analysis.merge(deals_analysis, on="Campaign", how="right").fillna(0)

# Removing "unknown" campaigns
campaign_performance = campaign_performance.query("Campaign != 'unknown'")

# conversion rate calculation (%)
campaign_performance["Conversion_Rate"] = (campaign_performance["Deals_Closed"] / campaign_performance["Leads_Generated"]) * 100
campaign_performance["Conversion_Rate"] = campaign_performance["Conversion_Rate"].fillna(0)  # Убираем NaNprint

# Set float display format
pd.options.display.float_format = '{:.2f}'.format
pd.set_option("display.max_rows", 20)

print(Back.YELLOW + "Summary table of campaign costs, leads, closed deals and conversion rate"+ Style.RESET_ALL)
display(campaign_performance.sort_values(by="Spend",ascending=False))

Summary table of campaign costs, leads, closed deals and conversion rate

import plotly.express as px

# Create a scatterplot "Costs vs. Deals" + size by number of leads + color scale by conversion rate
fig = px.scatter(
    campaign_performance,
    x="Spend", 
    y="Deals_Closed",
    #text="Campaign",  # Campaign names on the chart
    title="Marketing Campaigns Effiency: Costs vs. Deals + Leads + Conversion Rate",
    labels={"Spend": "Spend (€)", "Deals_Closed": "Deals Closed"},
    color="Conversion_Rate",  
    size="Leads_Generated",  
    hover_data=["Source","Campaign", "Spend", "Leads_Generated","Deals_Closed", "Conversion_Rate"],
    size_max=50  # maximum dot size
)

fig.update_traces(
    text=campaign_performance["Campaign"],
    textposition="top center",
    textfont_size=8
)
fig.show()

	Source	Leads	Success Deals	Conversion Rate (%)
2	Facebook Ads	4850	202.00	4.16
3	Google Ads	4226	173.00	4.09
5	Organic	2590	147.00	5.68
10	Tiktok Ads	2051	56.00	2.73
7	SMM	1730	91.00	5.26
12	Youtube Ads	1657	53.00	3.20
1	CRM	1656	24.00	1.45
0	Bloggers	1089	39.00	3.58
8	Telegram posts	1001	40.00	4.00
11	Webinar	379	26.00	6.86
6	Partnership	203	4.00	1.97
9	Test	159	3.00	1.89
4	Offline	2	0.00	0.00

	Source	Leads_Generated	Deals_Closed	Revenue	Average_Deal_Value	Closing_Duration	Conversion_Rate
2	Facebook Ads	4850	202	1042445.46	927.58	18.67	4.16
3	Google Ads	4226	173	829330.45	917.36	11.78	4.09
5	Organic	2590	147	657147.88	949.86	9.69	5.68
10	Tiktok Ads	2051	56	232650.00	879.50	13.00	2.73
7	SMM	1730	91	343071.96	903.56	15.29	5.26
12	Youtube Ads	1657	53	241093.49	980.86	12.81	3.20
1	CRM	1656	24	90950.00	913.66	11.94	1.45
0	Bloggers	1089	39	190256.82	936.50	17.51	3.58
8	Telegram posts	1001	40	177270.16	1012.21	16.41	4.00
11	Webinar	379	26	122500.91	804.95	21.72	6.86
6	Partnership	203	4	17580.00	1220.30	26.06	1.97
9	Test	159	3	11360.00	1031.11	13.50	1.89
4	Offline	2	0	0.00	0.00	0.00	0.00

	Campaign	Leads_Generated	Deals_Closed	Average_Deal_Value	Closing_Duration	Conversion_Rate
154	unknown	5526	271	927.12	13.16	4.90
118	performancemax_digitalmarkt_ru_DE	2653	112	905.72	12.48	4.22
152	youtube_shorts_DE	1635	53	980.86	12.88	3.24
18	12.07.2023wide_DE	1575	48	924.71	13.59	3.05
2	02.07.23wide_DE	975	52	889.20	18.73	5.33
...	...	...	...	...	...	...
99	grad_DE	1	0	0.00	1.00	0.00
91	eha_DE	1	0	0.00	0.00	0.00
113	marue_DE	1	0	0.00	0.00	0.00
141	web2410_DE	1	0	0.00	167.00	0.00
147	women_DE	1	0	0.00	69.00	0.00

	Campaign	Spend	Leads_Generated	Deals_Closed	Source	Conversion_Rate
119	performancemax_eng_DE	34183.45	245	1	Google Ads	0.41
152	youtube_shorts_DE	14149.22	1635	53	Youtube Ads	3.24
88	discovery_DE	9750.63	94	2	Google Ads	2.13
18	12.07.2023wide_DE	9471.52	1575	48	Tiktok Ads	3.05
2	02.07.23wide_DE	6913.60	975	52	Facebook Ads	5.33
...	...	...	...	...	...	...
147	women_DE	0.00	1	0	Bloggers	0.00
149	work_wr	0.00	6	0	Telegram posts	0.00
148	work_DE	0.00	317	10	Telegram posts	3.15
151	yo_DE	0.00	13	0	SMM	0.00
150	workingin_DE	0.00	48	0	Telegram posts	0.00

4 Анализ эффективности кампаний¶

Загрузка модулей и функций¶

4.1 Оцените эффективность различных маркетинговых источников (Source) в генерировании качественных лидов¶

Анализ количества лидов от каждого источника и коэффициента конверсии в первую покупку¶

Визуализация количества лидов и коэффициента конверсии для каждого источника¶

Для более глубокого анализа эффективности рекламных источников добавим в анализ число закрытых сделок и среднюю сумму сделки по источникам¶

Интерактивная диаграмма рассеяния показывает соотношение по рекламным источникам (выделены цветом):¶

Визуализация сравнения маркетинговых источников (Source) по среднему времени закрытия сделки (Deals_Closed) и средней сумме сделки (Average_Deal_Value)¶

Основные результаты анализа эффективности маркетинговых источников:¶

4.2 Сравните эффективность различных кампаний с точки зрения генерации лидов и коэффициента конверсии.¶

Рассчитаем показатели эффективности кампаний¶

Интерактивные диаграммы рейтинга кампаний по генерации лидов и корверсии¶

Результаты анализа эффективности маркетинговых кампаний с точки зрения генерации лидов и коэффициента конверсии:¶

4.3* Сравнение эффективности маркетинговых кампаний с точки зрения затрат на рекламу¶

Проверим соотвествие кампаний в датафреймах deals и spends¶

Анализ совпадений и различий в кампаниях между deals и spend¶

Проанализируем связь между затратами на маркетинговые кампании и их основными индикаторами эффективности¶

Визуализируем на интерактивной диаграмме зависимость затрат на рекламные кампании c количеством лидов, числом закрытых сделок и коэффифиентом конверсии¶

Результаты анализа¶